package com.gzhryc.jdbc.dialect;

import com.gzhryc.jdbc.ConnectionFactory;
import com.gzhryc.jdbc.DbHelper;
import com.gzhryc.jdbc.annotaion.*;
import com.gzhryc.jdbc.log.Logger;
import com.gzhryc.jdbc.models.IndexInfo;
import com.gzhryc.jdbc.utils.EntityUtils;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public abstract class AbsDbHelper implements DbHelper {

	Logger log = Logger.getLogger(AbsDbHelper.class);

	/**
	 * JDBC连接KEY
	 */
	protected String connectionKey;

	public AbsDbHelper(String connectionKey) {
		this.connectionKey = connectionKey;
	}

	/**
	 * 获取数据库连接对象
	 *
	 * @return
	 * @throws SQLException
	 */
	protected Connection getManageConn() throws SQLException {
		return ConnectionFactory.getManageConn(connectionKey);
	}

	protected Connection getConn() throws SQLException {
		return ConnectionFactory.getConn(connectionKey, true);
	}

	public boolean createDb(String database) throws SQLException {
		Connection conn = null;
		try {
			conn = getManageConn();
			Statement stmt = conn.createStatement();
			String sql = "CREATE DATABASE " + database + " DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci";
			log.sql(connectionKey,sql);
			if (stmt.executeUpdate(sql) > 0) {
				return true;
			}
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return false;
	}

	public boolean deleteDb(String database) throws SQLException {
		Connection conn = null;
		try {
			conn = getManageConn();
			Statement stmt = conn.createStatement();
			String sql = "DROP DATABASE " + database;
			log.sql(connectionKey,sql);
			stmt.execute(sql); // Mysql成功也返回false
			return true;
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	public boolean createTable(String tableName, Class<?> clazz) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();

			Statement stmt = conn.createStatement();

			StringBuilder sb = new StringBuilder("CREATE TABLE `" + tableName + "` (");
			// 遍历属性
			List<Field> fields = EntityUtils.findTableColumn(clazz);

			FieldCreateString fieldCreateString = getFieldCreateString(fields);
			sb.append(fieldCreateString.getSql());

			if (StringUtils.isNotBlank(fieldCreateString.getIdName())) {
				sb.append(",PRIMARY KEY (").append(fieldCreateString.getIdName()).append(")");
			}
			String temp = "engine=INNODB";
			if (fieldCreateString.getId() != null && fieldCreateString.getId().auto()) {
				long index = fieldCreateString.getId().autoIncrement();
				if (index > 0) {
					temp = temp + " AUTO_INCREMENT = " + index;
				}
			}
			sb.append(") ").append(temp).append(" DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci");
			String sql = sb.toString();
			log.sql(connectionKey,sql);
			stmt.execute(sql);
			return true;
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	// 将类属性转化成创建SQL的属性串
	public FieldCreateString getFieldCreateString(List<Field> fields) {
		FieldCreateString result = new FieldCreateString();
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < fields.size(); i++) {
			Field field = fields.get(i);
			Column col = field.getAnnotation(Column.class);
			Id id = field.getAnnotation(Id.class);
			PrimaryKey primaryKey = field.getAnnotation(PrimaryKey.class);
			if (id != null) {
				String idName = field.getName();
				if (col != null) {
					if (StringUtils.isNotBlank(col.name())) {
						idName = col.name();
					}
				}else {
					sb.append("`").append(idName).append("`");
					sb.append(" ").append(getDbTypeName(field.getType(), null));
					if (id.auto() && field.getType().equals(Long.class)) {
						sb.append(" NOT NULL AUTO_INCREMENT");
					} else {
						sb.append(" NOT NULL");
					}

				}
				result.setId(id);
				result.setIdName("`" + idName + "`");
			}

			if (col != null) {
				if (StringUtils.isNotBlank(col.name())) {
					sb.append("`").append(col.name()).append("`");
				} else {
					sb.append("`").append(field.getName()).append("`");
				}
				sb.append(" ").append(getDbTypeName(field.getType(), col));
				if (id != null && id.auto() && field.getType().equals(Long.class)) {
					sb.append(" NOT NULL AUTO_INCREMENT");
				} else if (col.notNull() || primaryKey != null || id != null || StringUtils.isNotBlank(col.defVal())) {
					sb.append(" NOT NULL");
				} else {
					sb.append(" NULL");
				}
				if (col.autoUpdateTime()) {
					sb.append(" DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
				} else if (StringUtils.isNotBlank(col.defVal())) {
					sb.append(" DEFAULT '").append(col.defVal()).append("'");
				}
				if (col != null && StringUtils.isNotBlank(col.note())) {
					sb.append(" COMMENT '").append(col.note()).append("'");
				}
			}

			// 添加复合主键
			if (primaryKey != null) {
				assert col != null;
				String idName = field.getName();
				if (StringUtils.isNotBlank(col.name())) {
					idName = col.name();
				}
				if (StringUtils.isNotBlank(result.getIdName())) {
					result.setIdName(result.getIdName() + ",`" + idName + "`");
				} else {
					result.setIdName("`" + idName + "`");
				}
			}

			if (i < fields.size() - 1) {
				sb.append(",");
			}
		}
		result.setSql(sb.toString());
		return result;
	}

	public boolean createTable(Class<?> clazz) throws SQLException {
		String tableName = clazz.getSimpleName();
		// 获取表名
		Table table = clazz.getAnnotation(Table.class);
		if (table != null && StringUtils.isNotBlank(table.name())) {
			tableName = table.name();
		}
		return createTable(tableName, clazz);
	}

	public boolean renameTable(String tableName, String newTableName) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			Statement stmt = conn.createStatement();
			String sql = "RENAME TABLE " + tableName + " TO " + newTableName;
			log.sql(connectionKey,sql);
			stmt.execute(sql);
			return true;
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	public boolean createView(Class<?> clazz) throws SQLException {
		String viewName = clazz.getSimpleName();
		// 获取表名
		View view = clazz.getAnnotation(View.class);
		if (view != null && StringUtils.isNotBlank(view.name())) {
			viewName = view.name();
			return createView(viewName, view.as());
		}
		return false;
	}

	public boolean createView(String viewName, String asSql) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			Statement stmt = conn.createStatement();
			String sql = "CREATE VIEW `" + viewName + "` AS " + asSql;
			log.sql(connectionKey,sql);
			stmt.execute(sql);
			return true;
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	/**
	 * 创建表的索引
	 *
	 * @param clazz
	 * @return
	 * @throws SQLException
	 * @author zhanghao 2020-5-15 创建
	 */
	public void createOrUpdateIndex(String tableName, Class<?> clazz) throws SQLException {
		Map<String, List<IndexColumn>> indexList = new HashMap<String, List<IndexColumn>>();
		// 遍历属性
		List<Field> fields = EntityUtils.findTableColumn(clazz);
		for (Field field : fields) {
			Index index = field.getAnnotation(Index.class);
			if (index != null) {
				String[] names = index.name();
				int[] priorities = index.priority();
				if (names.length > 0 && names.length == priorities.length) {
					for (int i = 0; i < names.length; i++) {
						List<IndexColumn> fieldList = indexList.computeIfAbsent(names[i], k -> new ArrayList<IndexColumn>());
						fieldList.add(new IndexColumn(field, priorities[i]));
					}
				}
			}
		}

		if (indexList.size() > 0) {
			// 检查表索引
			List<IndexInfo> oldIndexList = findAllIndex(tableName);
			for (String name : indexList.keySet()) {
				List<IndexColumn> fieldList = indexList.get(name);
				// 排序
				Collections.sort(fieldList);
				StringBuilder sb = new StringBuilder();
				// 检查索引字段是否一致
				for (IndexColumn item : fieldList) {
					sb.append(",").append(EntityUtils.getName(item.getField()));
				}
				String columns = sb.substring(1);
				boolean flag = true;
				if (oldIndexList != null) {
					for (IndexInfo info : oldIndexList) {
						if (info.getKeyName().equals(name)) {
							String temp = StringUtils.join(info.getColumnNames(), ",");
							if (!columns.equals(temp)) {
								// 删除索引重新创建
								deleteIndex(tableName, name);
							} else {
								flag = false;
							}
						}
					}
				}
				if (flag) {
					String sql = "CREATE INDEX " + name + " ON " + tableName + "(" + columns + ")";
					Connection conn = null;
					try {
						conn = getConn();
						Statement stmt = conn.createStatement();
						log.sql(connectionKey,sql);
						stmt.execute(sql);
					} finally {
						if (conn != null) {
							conn.close();
						}
					}
				}
			}
		}
	}

	public void createOrUpdateUniqueKey(String database,String tableName, Class<?> clazz) throws SQLException {
		// 遍历属性
		List<Field> fields = EntityUtils.findTableColumn(clazz);
		Map<String, List<Field>> uniqueKeyMap = new HashMap<>();
		for (Field field : fields) {
			UniqueKey uniqueKey = field.getAnnotation(UniqueKey.class);
			if (uniqueKey != null) {
				String name = uniqueKey.name();
				if (StringUtils.isBlank(name)) {
					name = field.getName();
				}
				name = "uk_" + name;
				List<Field> fieldList = uniqueKeyMap.get(name);
				if (fieldList != null) {
					fieldList.add(field);
				} else {
					fieldList = new ArrayList<>();
					fieldList.add(field);
					uniqueKeyMap.put(name, fieldList);
				}
			}
		}

		if (uniqueKeyMap.size() > 0) {
			//获取当前索引
			List<IndexInfo> oldIndexList = findAllIndex(tableName);
			for (String name : uniqueKeyMap.keySet()) {
				List<Field> fieldList = uniqueKeyMap.get(name);
				boolean flag = true;
				if (oldIndexList != null) {
					for (IndexInfo info : oldIndexList) {
						if (info.getKeyName().equals(name)) {
							if (info.getColumnNames().size() != fieldList.size()) {
								// 删除索引重新创建
								deleteIndex(tableName, name);
							} else {
								flag = false;
								for (Field field : fieldList) {
									String colName = EntityUtils.getName(field);
									if (!info.getColumnNames().contains(colName)) {
										// 删除索引重新创建
										deleteIndex(tableName, name);
										flag = true;
										break;
									}
								}
							}
						}
					}
				}

				if (flag) {
					StringBuilder sb = new StringBuilder();
					// 检查索引字段是否一致
					for (Field field : fieldList) {
						sb.append(",").append(EntityUtils.getName(field));
					}
					String columns = sb.substring(1);
					String sql = "ALTER TABLE " + tableName + " ADD CONSTRAINT " + name + " UNIQUE (" + columns + ")";
					Connection conn = null;
					try {
						conn = getConn();
						Statement stmt = conn.createStatement();
						log.sql(connectionKey, sql);
						stmt.execute(sql);
					} finally {
						if (conn != null) {
							conn.close();
						}
					}
				}
			}
		}

		List<String> oldUniqueKeyNames = findAllUniqueKeyName(database,tableName);
		if(oldUniqueKeyNames != null && oldUniqueKeyNames.size() > 0){
			for(String name : oldUniqueKeyNames){
				if(!uniqueKeyMap.containsKey(name)){
					// 删除旧的索引
					deleteIndex(tableName, name);
				}
			}
		}
	}

	protected String getDbTypeName(Class<?> type, Column col) {
		if (col != null && StringUtils.isNotBlank(col.type())) {
			return col.type();
		} else if (Integer.class.equals(type) || int.class.equals(type)) {
			if (col == null || col.length() == 0) {
				return "INT(8)";
			} else {
				return "INT(" + col.length() + ")";
			}
		} else if (Long.class.equals(type) || long.class.equals(type)) {
			return "BIGINT(11)";
		} else if (Float.class.equals(type) || float.class.equals(type)) {
			return "FLOAT";
		} else if (Double.class.equals(type) || double.class.equals(type)) {
			if (col != null) {
				if (col.length() == 0) {
					return "DOUBLE(8," + col.decimalLen() + ")";
				} else {
					return "DOUBLE(" + col.length() + "," + col.decimalLen() + ")";
				}
			} else {
				return "DOUBLE(8,2)";
			}
		} else if (Boolean.class.equals(type) || boolean.class.equals(type)) {
			return "TINYINT(1)";
		} else if (Date.class.equals(type)) {
			if (col != null && col.autoUpdateTime()) {
				return "TIMESTAMP";
			} else {
				return "DATETIME";
			}
		} else if (col != null && col.length() != 0) {
			return "VARCHAR(" + col.length() + ")";
		} else {
			return "VARCHAR(32)";
		}
	}

	/**
	 * 删除表
	 */
	public boolean deleteTable(String tableName) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			Statement stmt = conn.createStatement();
			String sql = "DROP TABLE " + tableName;
			log.sql(connectionKey,sql);
			stmt.execute(sql); // Mysql成功也返回false
			return true;
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	/**
	 * 删除索引
	 *
	 * @param tableName
	 * @param indexName
	 * @return
	 * @throws SQLException
	 */
	public boolean deleteIndex(String tableName, String indexName) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			Statement stmt = conn.createStatement();
			String sql = "DROP INDEX " + indexName + " ON " + tableName;
			log.sql(connectionKey, sql);
			stmt.execute(sql);
			return true;
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	public static class FieldCreateString {

		private String sql;
		private Id id;
		private String idName;

		public String getSql() {
			return sql;
		}

		public void setSql(String sql) {
			this.sql = sql;
		}

		public Id getId() {
			return id;
		}

		public void setId(Id id) {
			this.id = id;
		}

		public String getIdName() {
			return idName;
		}

		public void setIdName(String idName) {
			this.idName = idName;
		}
	}

	private static class IndexColumn implements Comparable<IndexColumn> {

		private Integer priority;
		private Field field;

		public IndexColumn(Field field, Integer priority) {
			this.field = field;
			this.priority = priority;
		}

		public Integer getPriority() {
			return priority;
		}

		public Field getField() {
			return field;
		}

		@Override
		public int compareTo(IndexColumn o) {
			return priority > o.getPriority() ? 1 : -1;
		}
	}
}
